package com.chidopi.market.bookstore.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.chidopi.cdf.dao.AbstractBaseDaoImpl;
import com.chidopi.market.bookstore.domain.BookCities;
import com.chidopi.market.bookstore.domain.Subscriptions;

public class SubscriptionsDaoImpl extends AbstractBaseDaoImpl<Subscriptions>  {
	
	private static Logger logger = LoggerFactory.getLogger(SubscriptionsDaoImpl.class);

	@Override
	public int insert(Subscriptions t) {
		String sql = "insert into subscriptions (NAME, DESCRIPTION, ENABLE_FLAG, START_TIME, END_TIME, PERIOD_UNIT, PERIOD_LENGTH, " +
				                                "PERIOD_QUOTA, PERIOD_AMOUNT, APPLY_OS, CITY_ID, CREATE_DATE, CREATE_BY) "
				                     + "values (:name, :description, :enable_flag, :start_time, :end_time, :period_unit, :period_length, " +
				                               ":period_quota, :period_amount, :apply_os, :bookCity.id, NOW(), :create_by)";
		return super.execSQL(sql, t);
	}

	@Override
	public int update(Subscriptions t) {
		String sql = "update subscriptions set NAME = :name, DESCRIPTION = :description, ENABLE_FLAG = :enable_flag, START_TIME = :start_time, PERIOD_UNIT = :period_unit, PERIOD_LENGTH = :period_length," +
				                             " PERIOD_QUOTA = :period_quota, PERIOD_AMOUNT = :period_amount, APPLY_OS = :apply_os, CITY_ID = :bookCity.id, update_by = :update_by where id= :id";
		return super.execSQL(sql, t);
	}

	@Override
	public int delete(Subscriptions t) {
		String sql = "delete from subscriptions where id = :id";
		return super.execSQL(sql, t);
	}

	@Override
	public List<Subscriptions> find(String sqlWhere, Object... args) {
		if (sqlWhere == null || sqlWhere.trim().equals("")) {
			String sql = "select a.*, b.CITYNAME from subscriptions a left join bookcities b on a.CITY_ID = b.ID order by a.ID ";
			return super.simpleJdbcTemplate.query(sql, this.getRowMapper());
		} else {
			logger.debug("sqlWhere:[" + sqlWhere + "]/params:" + Arrays.asList(args).toString());
			String sql = "select a.*, b.CITYNAME from subscriptions a left join bookcities b on a.CITY_ID = b.ID where " + sqlWhere + " order by a.ID ";
			logger.debug("sql: " + sql);
			List<Subscriptions> list = super.simpleJdbcTemplate.query(sql, this.getRowMapper(), args);
			if (list == null) {
				logger.debug("query null!!!");
			} else {
				for (Subscriptions subscriptions : list) {
					logger.debug("id=" + subscriptions.getId() + "/name=" + subscriptions.getName());
				}
			}
			return list;
		}
	}

	@Override
	public Subscriptions findById(Object id) {
		String sql = "a.id=?";
		List<Subscriptions> list = this.find(sql, id);
		if (0 == list.size()) return null;
		return list.get(0);
	}

	@Override
	protected String getTable() {
		return "subscriptions";
	}

	@Override
	protected String getSequenceName() {
		return null;
	}

	@Override
	protected RowMapper<Subscriptions> getRowMapper() {
		return new ParameterizedRowMapper<Subscriptions>(){
			@Override
			public Subscriptions mapRow(ResultSet rs, int rowNum)	throws SQLException {
				Subscriptions vo = new Subscriptions();
				vo.setId(rs.getInt("ID"));
				vo.setName(rs.getString("NAME"));
				vo.setDescription(rs.getString("DESCRIPTION"));
				vo.setEnable_flag(rs.getString("ENABLE_FLAG"));
				vo.setStart_time(rs.getDate("START_TIME"));
				vo.setEnd_time(rs.getDate("END_TIME"));
				vo.setPeriod_unit(rs.getString("PERIOD_UNIT"));
				vo.setPeriod_length(rs.getInt("PERIOD_LENGTH"));
				vo.setPeriod_quota(rs.getInt("PERIOD_QUOTA"));
				vo.setPeriod_amount(rs.getFloat("PERIOD_AMOUNT"));
				vo.setApply_os(rs.getInt("APPLY_OS"));
				BookCities bookCity = new BookCities();
				bookCity.setId(rs.getInt("CITY_ID"));
				bookCity.setCityname(rs.getString("CITYNAME"));
				vo.setBookCity(bookCity);				
				return vo;
			}
		};	
    }

}
